﻿CREATE FUNCTION [dbo].[fs_StateParamValue]
(	@StateID int,
	@Param sysname
)RETURNS sql_variant WITH SCHEMABINDING
AS
BEGIN

IF ISNUMERIC(@Param) = 0 SELECT @Param = ID FROM dbo.ts_StateParameter (NOLOCK) WHERE Name = @Param

RETURN (
SELECT     ISNULL(S_P.StateParameterValue, S_P_P.StateParameterValue) AS StateParameterValue
FROM         dbo.t_State_Parameters AS S_P WITH (NOLOCK) RIGHT OUTER JOIN
                      dbo.t_State AS S ON S_P.StateID = S.ID AND S_P.StateParameterID = @Param LEFT OUTER JOIN
                      dbo.t_State_Parameters AS S_P_P ON S.ParentID = S_P_P.StateID AND S_P_P.StateParameterID = @Param
WHERE     (S.ID = @StateID))
END



